package com.imut.lagain.repository;
import org.apache.ibatis.annotations.Select;


import com.imut.lagain.entity.AnxietyReminder;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.Modifying;


import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.List;
import java.util.Optional;

/**
 * 焦虑安抚提醒数据访问层
 */
@Mapper
public interface AnxietyReminderRepository extends BaseMapper<AnxietyReminder> {

    /**
     * 根据用户ID查询提醒
     */
    @Select("SELECT a FROM AnxietyReminder a WHERE a.userId = :userId AND a.isDeleted = false ORDER BY a.createdAt DESC")
    List<AnxietyReminder> findByUserId(@Param("userId") Long userId);

    /**
     * 根据用户ID查询启用的提醒
     */
    @Select("SELECT a FROM AnxietyReminder a WHERE a.userId = :userId AND a.isEnabled = true AND a.isDeleted = false ORDER BY a.timeOfDay ASC")
    List<AnxietyReminder> findEnabledByUserId(@Param("userId") Long userId);

    /**
     * 根据用户ID和提醒类型查询
     */
    @Select("SELECT a FROM AnxietyReminder a WHERE a.userId = :userId AND a.reminderType = :reminderType AND a.isDeleted = false ORDER BY a.createdAt DESC")
    List<AnxietyReminder> findByUserIdAndReminderType(@Param("userId") Long userId, @Param("reminderType") String reminderType);

    /**
     * 根据提醒ID查询
     */
    @Select("SELECT a FROM AnxietyReminder a WHERE a.id = :reminderId AND a.isDeleted = false")
    Optional<AnxietyReminder> findByReminderId(@Param("reminderId") Long reminderId);

    /**
     * 查询需要触发的提醒
     */
    @Select("SELECT a FROM AnxietyReminder a WHERE a.isEnabled = true AND a.nextTrigger <= :currentTime AND a.isDeleted = false")
    List<AnxietyReminder> findTriggeredReminders(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 查询特定时间点的提醒
     */
    @Select("SELECT a FROM AnxietyReminder a WHERE a.isEnabled = true AND a.timeOfDay = :timeOfDay AND a.isDeleted = false")
    List<AnxietyReminder> findByTimeOfDay(@Param("timeOfDay") LocalTime timeOfDay);

    /**
     * 更新提醒触发信息
     */
    @Modifying
    @Select("UPDATE AnxietyReminder a SET a.lastTriggered = :lastTriggered, a.nextTrigger = :nextTrigger, a.triggerCount = a.triggerCount + 1, a.updatedAt = :updatedAt WHERE a.id = :reminderId")
    int updateTriggerInfo(@Param("reminderId") Long reminderId, 
                         @Param("lastTriggered") LocalDateTime lastTriggered, 
                         @Param("nextTrigger") LocalDateTime nextTrigger, 
                         @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 更新提醒响应计数
     */
    @Modifying
    @Select("UPDATE AnxietyReminder a SET a.responseCount = a.responseCount + 1, a.updatedAt = :updatedAt WHERE a.id = :reminderId")
    int incrementResponseCount(@Param("reminderId") Long reminderId, @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 更新提醒效果评分
     */
    @Modifying
    @Select("UPDATE AnxietyReminder a SET a.effectivenessRating = :effectivenessRating, a.updatedAt = :updatedAt WHERE a.id = :reminderId")
    int updateEffectivenessRating(@Param("reminderId") Long reminderId, 
                                 @Param("effectivenessRating") Double effectivenessRating, 
                                 @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 启用/禁用提醒
     */
    @Modifying
    @Select("UPDATE AnxietyReminder a SET a.isEnabled = :isEnabled, a.updatedAt = :updatedAt WHERE a.id = :reminderId")
    int updateReminderStatus(@Param("reminderId") Long reminderId, 
                            @Param("isEnabled") Boolean isEnabled, 
                            @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 统计用户提醒数量
     */
    @Select("SELECT COUNT(a) FROM AnxietyReminder a WHERE a.userId = :userId AND a.isDeleted = false")
    Long countByUserId(@Param("userId") Long userId);

    /**
     * 统计用户启用的提醒数量
     */
    @Select("SELECT COUNT(a) FROM AnxietyReminder a WHERE a.userId = :userId AND a.isEnabled = true AND a.isDeleted = false")
    Long countEnabledByUserId(@Param("userId") Long userId);

    /**
     * 统计用户各类型提醒数量
     */
    @Select("SELECT a.reminderType, COUNT(a) FROM AnxietyReminder a WHERE a.userId = :userId AND a.isEnabled = true AND a.isDeleted = false GROUP BY a.reminderType")
    List<Object[]> countByUserIdAndReminderType(@Param("userId") Long userId);

    /**
     * 查询用户提醒响应率
     */
    @Select("SELECT AVG(CAST(a.responseCount AS DOUBLE) / CAST(a.triggerCount AS DOUBLE)) FROM AnxietyReminder a WHERE a.userId = :userId AND a.triggerCount > 0 AND a.isDeleted = false")
    Double getAverageResponseRate(@Param("userId") Long userId);

    /**
     * 查询用户平均效果评分
     */
    @Select("SELECT AVG(a.effectivenessRating) FROM AnxietyReminder a WHERE a.userId = :userId AND a.effectivenessRating IS NOT NULL AND a.isDeleted = false")
    Double getAverageEffectivenessRating(@Param("userId") Long userId);

    /**
     * 查询最有效的提醒类型
     */
    @Select("SELECT a.reminderType FROM AnxietyReminder a WHERE a.userId = :userId AND a.effectivenessRating >= 4.0 AND a.isDeleted = false GROUP BY a.reminderType ORDER BY COUNT(a) DESC")
    List<String> getMostEffectiveReminderTypes(@Param("userId") Long userId);

    /**
     * 查询需要自动禁用的提醒
     */
    @Select("SELECT a FROM AnxietyReminder a WHERE a.isEnabled = true AND a.autoDisableAfter IS NOT NULL AND a.createdAt < :disableThreshold AND a.isDeleted = false")
    List<AnxietyReminder> findRemindersToAutoDisable(@Param("disableThreshold") LocalDateTime disableThreshold);

    /**
     * 查询低响应率的提醒
     */
    @Select("SELECT a FROM AnxietyReminder a WHERE a.userId = :userId AND a.triggerCount >= 5 AND (CAST(a.responseCount AS DOUBLE) / CAST(a.triggerCount AS DOUBLE)) < 0.3 AND a.isDeleted = false")
    List<AnxietyReminder> findLowResponseRateReminders(@Param("userId") Long userId);

    /**
     * 查询用户在特定时间段的提醒
     */
    @Select("SELECT a FROM AnxietyReminder a WHERE a.userId = :userId AND a.timeOfDay BETWEEN :startTime AND :endTime AND a.isEnabled = true AND a.isDeleted = false")
    List<AnxietyReminder> findByUserIdAndTimeRange(@Param("userId") Long userId, 
                                                   @Param("startTime") LocalTime startTime, 
                                                   @Param("endTime") LocalTime endTime);

    /**
     * 查询紧急提醒
     */
    @Select("SELECT a FROM AnxietyReminder a WHERE a.userId = :userId AND a.reminderType = 'emergency' AND a.isEnabled = true AND a.isDeleted = false")
    List<AnxietyReminder> findEmergencyReminders(@Param("userId") Long userId);

    /**
     * 批量更新下次触发时间
     */
    @Modifying
    @Select("UPDATE AnxietyReminder a SET a.nextTrigger = :nextTrigger, a.updatedAt = :updatedAt WHERE a.id IN :reminderIds")
    int batchUpdateNextTrigger(@Param("reminderIds") List<Long> reminderIds, 
                              @Param("nextTrigger") LocalDateTime nextTrigger, 
                              @Param("updatedAt") LocalDateTime updatedAt);
}
